﻿
CREATE PROC Inv.spGeneraCodigo
	@psCodigo VARCHAR(14) = NULL OUTPUT,
	@psCodigo1 VARCHAR(7) = NULL OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @sCadena VARCHAR(7), @nIndice INT, @nDosUno INT, @nMul INT, @sSum NVARCHAR(500), @sMul VARCHAR(50)
	DECLARE	@tblDigVerif TABLE(Digito INT)
	-- 1
	IF NULLIF(@psCodigo,'') IS NOT NULL
		SET @sCadena = @psCodigo
	ELSE
		SELECT @sCadena = RIGHT('000000' + CAST(CAST(RAND()*1000000 AS INT) AS VARCHAR), 6)
	
	SET @nIndice = LEN(@sCadena)
	SET @nDosUno = 2
	WHILE @nIndice >= 1
	BEGIN
		SELECT @nMul = CAST(SUBSTRING(@sCadena, @nIndice, 1) AS INT) * @nDosUno
		IF @nDosUno = 2
			SET @nDosUno = 1
		ELSE
			SET @nDosUno = 2
		IF LEN(CAST(@nMul AS VARCHAR)) = 2
			SELECT @sMul = SUBSTRING(CAST(@nMul AS VARCHAR), 1, 1) + ' + ' + SUBSTRING(CAST(@nMul AS VARCHAR), 2, 1)
		ELSE
			set @sMul = CAST(@nMul AS VARCHAR)
			
		SET @sSum = ISNULL(@sSum + ' + ','') + @sMul
		SET @nIndice = @nIndice - 1
	END
	
	SET @sSum = 'Select 10 - ((' + @sSum + ')% 10)'
	
	INSERT INTO @tblDigVerif
	EXEC sp_executesql @sSum
	SELECT	@psCodigo = @sCadena + CAST(Digito AS VARCHAR)
	FROM	@tblDigVerif
	SET @psCodigo1 = @psCodigo
	
	-- 2
	SELECT @sCadena = RIGHT('000000' + CAST(CAST(RAND()*1000000 AS INT) AS VARCHAR), 6)
	SET @nIndice = LEN(@sCadena)
	SET @nDosUno = 2
	SET @sSum = NULL
	SET @nMul = ''
	WHILE @nIndice >= 1
	BEGIN
		SELECT @nMul = CAST(SUBSTRING(@sCadena, @nIndice, 1) AS INT) * @nDosUno
		IF @nDosUno = 2
			SET @nDosUno = 1
		ELSE
			SET @nDosUno = 2
		IF LEN(CAST(@nMul AS VARCHAR)) = 2
			SELECT @sMul = SUBSTRING(CAST(@nMul AS VARCHAR), 1, 1) + ' + ' + SUBSTRING(CAST(@nMul AS VARCHAR), 2, 1)
		ELSE
			set @sMul = CAST(@nMul AS VARCHAR)
			
		SET @sSum = ISNULL(@sSum + ' + ','') + @sMul
		SET @nIndice = @nIndice - 1
	END
	
	SET @sSum = 'Select 10 - ((' + @sSum + ')% 10)'
	--SELECT @sSum
	
	DELETE FROM @tblDigVerif
	INSERT INTO @tblDigVerif
	EXEC sp_executesql @sSum
	
	--DECLARE @sSum NVARCHAR(50)
	--SET @sSum  = 'Select 10 - ((2 + 7 + 1 + 6 + 7 + 1 + 4 + 5)% 10)'
	--EXEC sp_executesql @sSum
	
	DECLARE @nCodigo2 VARCHAR(7)
	SELECT	@nCodigo2 = @sCadena + CAST(Digito AS VARCHAR)
	FROM	@tblDigVerif
	
	--SELECT @psCodigo + @nCodigo2 AS Codigo
	SELECT @psCodigo = @psCodigo + @nCodigo2
	
	IF EXISTS(SELECT 1 FROM Inv.CuponRegistro WHERE CodigoCupon = @psCodigo)
	BEGIN
		SET @psCodigo = NULL
		EXEC Inv.spGeneraCodigo
			@psCodigo = @psCodigo OUTPUT, -- varchar(14)
		    @psCodigo1 = NULL -- varchar(7)
	END
	
	PRINT @psCodigo
	SET NOCOUNT OFF
END